package com.dtt.company.project.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.dtt.company.department.model.DepartmentModel;
import com.dtt.company.project.model.ProjectModel;
import com.dtt.company.util.JDBCUtil;

public class ProjectDao {
	
	private String table="project";
	private String col="code,name,time";
	private Connection conn=null;
	private PreparedStatement ps=null;
	
	public Integer insert(ProjectModel model) {
		StringBuffer sql=new StringBuffer("insert into ");
		sql.append(table).append('(').append(col).append(')').append("value(?,?,?)");
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			ps.setString(1, model.getCode());
			ps.setString(2, model.getName());
			ps.setString(3, model.getTime());
			
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	public Integer delete(ProjectModel model) {
		StringBuffer sql=new StringBuffer("delete from ");
		sql.append(table);
		List<Object> vals=appendWhere(sql,model);
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) 
				ps.setObject(i+1, vals.get(i));				
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	public Integer update(ProjectModel model) {
		StringBuffer sql=new StringBuffer("update ");
		sql.append(table);
		List<Object> vals=appendUpdate(sql,model);
		conn=JDBCUtil.getConnection();
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) 
				ps.setObject(i+1, vals.get(i));				
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}
	
	public List<ProjectModel> select(ProjectModel model){
		StringBuffer sql=new StringBuffer("select ").append(col).append(" from ");
		sql.append(table);//code,name,time
		List<Object> vals=appendWhere(sql,model);
//		System.out.println(sql.toString());
		List<ProjectModel> list=new ArrayList<>();
		conn=JDBCUtil.getConnection();
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) {
				ps.setObject(i+1, vals.get(i));	
//				System.out.println(vals.get(i));
			}				
//			System.out.println(sql.toString());
			rs= ps.executeQuery();
			while(rs.next()) {
				ProjectModel md=new ProjectModel();
				md.setCode(rs.getString("code"));
				md.setName(rs.getString("name"));
				md.setTime(rs.getString("time"));
				list.add(md);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps,rs);
		}
//		System.out.println("list==="+list);
//		if(list==null)System.out.println("list空");
//		else if(list.isEmpty())System.out.println("list长度0");
//		else System.out.println("list非空");
		return list;
		
	}
	
	public int selectCount(ProjectModel model){
		StringBuffer sql=new StringBuffer("select count(id) from ");
		sql.append(table);
		List<Object> vals=appendWhere(sql,model);
		List<ProjectModel> list=new ArrayList<>();
		conn=JDBCUtil.getConnection();
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size();i++) {
				ps.setObject(i+1, vals.get(i));	
//				System.out.println(vals.get(i));
			}				
//			System.out.println(sql.toString());
			rs= ps.executeQuery();
			if(rs.next())
				return rs.getInt(1);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps,rs);
		}
		return 0;
		
	}
	
	public List<Object> appendWhere(StringBuffer sql, ProjectModel model) {
		sql.append(" where 1=1");
		List<Object> objs=new ArrayList<Object>();
		//code,name,time
		String code=model.getCode();
		if(code!=null&&!code.trim().isEmpty()) {//!"".equals(code.trim())
			sql.append(" and code like ?");
			objs.add(code);
		}		
		String name=model.getName();
		if(name!=null&&!name.trim().isEmpty()) {
			sql.append(" and name like ?");
			objs.add(name);
		}
		String time=model.getTime();
		if(time!=null&&!time.trim().isEmpty()) {
			sql.append(" and time like ?");
			objs.add(time);
		}
		String order=model.getOrderby();
		if(order!=null&&!order.trim().isEmpty()) {
			sql.append(" order by ").append(order);
			//不能用？，因为？上的数据带引号
		}	
		if(model.isPageOn()) {
			sql.append(" limit ?,? ");
			objs.add(model.getRowStart());
			objs.add(model.getPageLimit());//objs.add(model.getRowCount());
		}
		return objs;
	}
	public List<Object> appendUpdate(StringBuffer sql,ProjectModel model ){
		List<Object> objs=new ArrayList<Object>();//code,name,time
		sql.append(" set id=id");		
		String name=model.getName();
		if(name!=null) {
			sql.append(",name=?");
			objs.add(name);
		}
		String time=model.getTime();
		if(time!=null) {
			sql.append(",time=?");
			objs.add(time);
		}
		String code=model.getCode();
		sql.append(" where code=?");
		objs.add(code);
		return objs;
	}
	public static void main(String[] args) {
		ProjectModel model=new ProjectModel();
		model.setCode("%pj%");
//		model.setName("项目五");
		Date d=new Date();
//		model.setTime(d.toLocaleString());
		System.out.println("===="+model.toString());
		ProjectDao dao=new ProjectDao();
//		Integer r=dao.insert(model);
//		Integer r=dao.update(model);
		List<ProjectModel> list=dao.select(model);
//		Integer r=dao.delete(model);
//		System.out.println(r);
		if(list!=null) {
			for(ProjectModel li:list) {
				System.out.println(li.toString());
			}
		}
		else System.out.println("空");
		
	}

}
